Azure SQL Database introduces Regex-based dynamic data masking, a new capability that enables flexible, pattern-driven masking for string-based columns using regular expressions through the T‑SQL REGEXP_REPLACE() function. This feature extends Dynamic Data Masking beyond built-in masking functions, giving you precise control over which portions of sensitive data are masked and which remain visible, helping preserve data utility while meeting business and compliance requirements.
This capability is especially useful when working with structured data patterns—such as emails, phone numbers, or identifiers—where teams need to preserve specific visible segments while masking sensitive parts, and where built-in masks may be rigid for some business workflows.
Why this matters
Dynamic Data Masking helps reduce accidental exposure of sensitive data by obfuscating values in query results for nonprivileged users, while keeping the original data intact in the database. However, existing built-in masking functions—such as default(), email(), random(), partial(), and datetime()—apply fixed patterns. These patterns cannot be customized, and may not be suitable for some of the real-world scenarios.
For example, the built-in email() mask always transforms an address like alice.johnson@example.com into aXXX@XXXX.com, fully obscuring the domain name. In many operational scenarios, retaining the domain name is important for troubleshooting, routing, or business logic.
Regex-based dynamic data masking addresses this gap by enabling precision masking without sacrificing usability.
What’s new with regex-based masking
Regex-based DDM allows you to define custom, pattern-driven masking rules using regular expressions. By leveraging the native REGEXP_REPLACE function in Azure SQL Database, you can precisely specify which parts of a string to mask and which to preserve—centrally enforced in the database layer.
This approach supports variable-length and structured string data, enabling more precise and flexible data masking rules.
Practical scenarios
Regex-based masking enables common customer scenarios that are difficult to address with fixed masks:
- Mask email usernames while preserving domains
alice.johnson@example.com → ****@example.com - Preserve country codes in phone numbers
+1-4155552671 → +1-XXXXXXXXXX
+44-7911123456 → +44-XXXXXXXXXX - Mask structured identifiers consistently
Hide sensitive portions of national IDs or custom identifiers while keeping recognizable structure for support and auditing workflows. For example: AB-1234-5678 → AB-****-5678
Example
The following example creates a table CustomerDetails with regex-based masking applied on Phone_Number and Email columns. The phone number mask preserves the country code and replaces the remaining digits with xxxx, and the email mask conceals the username while retaining the domain name.
|
-- Drop the CustomerDetails table if it exists DROP TABLE IF EXISTS Data.CustomerDetails; -- Create a CustomerDetails table under a schema CREATE TABLE Data.CustomerDetails ( ID INT IDENTITY(1,1) PRIMARY KEY, Name varchar(30), Phone_Number varchar(30) MASKED WITH (FUNCTION = 'REGEXP_REPLACE("(\+\d{1,3})(?:[ -.]?\d){7,14}","(\1)-xxxx")'), Email varchar(255) MASKED WITH (FUNCTION = 'REGEXP_REPLACE("([a-zA-Z0-9._%+-]+)(@+)([a-zA-Z0-9.-]+)(\.)(\w)","*****\2\3\4\5")') ); -- Insert some dummy records to CustomerDetails table INSERT INTO Data.CustomerDetails (Name, Phone_Number, Email) VALUES ('Alice Johnson', '+1 202-555-0123', 'alice.johnson@example.com'), ('Bob Smith', '+1 415-555-0198', 'bob.smith@contoso.com'); -- Create a test user CREATE USER SupportEngineer WITHOUT LOGIN; -- Grant read permission on CustomerDetails to SupportEngineer GRANT SELECT ON Data.CustomerDetails TO SupportEngineer; -- Query CustomerDetails table as SupportEngineer EXECUTE AS USER = 'SupportEngineer'; SELECT * FROM Data.CustomerDetails; REVERT; |
Public Preview notice
Important
Regex-based dynamic data masking is currently in Preview for Azure SQL Database. Preview features are provided for evaluation purposes and are subject to the Preview Terms Of Use | Microsoft Azure.
Azure SQL Database is the first SQL offering to receive this feature, with additional SQL platforms planned in the future.
How to get started
To learn more and access sample scripts, refer to the official documentation Regex-based dynamic data masking (preview) - Azure SQL Database | Microsoft Learn
Try Regex-based dynamic data masking in your dev or test environment and tell us what works—and what doesn’t!
Tell us what you need next in Data Masking
Share your ideas through Azure SQL feedback forum.
Disclaimer: The examples in this article use fictional customer records created solely for demonstration and testing purposes. No real customer data is included.
Microsoft